Modify Unit Creation Form

Description

The Create Unit form allows you to add a unit to the system based on information that you enter or select. This could include details such as the serial number, color or build group.

The form can include check boxes, combo boxes, list boxes that are populated with data from the database. You can then make a selection from that list.

Alternatively, or in addition, it can contain a text box where you can enter a customized value.

In this tutorial we will select the color of the unit that is being manufactured and enter a serial number for it.

How to Set Up the Form

Create Stored Procedure for Unit Creation

In order to insert a new unit in the database you will need to use a stored procedure. As with all customised stored procedures it must start with the prefix _custom. In this case we will use custom_create_new_unit.

Add this stored procedurethis stored procedure to your database.

ALTER PROCEDURE [dbo].[custom_create_new_unit_tutorial]

@productCode nvarchar(20),

@serial nvarchar(20),

@colorID int,

@unitID int output

begin

set nocount on

declare

@plantID int,

@productID int,

@buildGroup int,

@modelyear smallint,

@prodDate date

set @unitID = -1

if (@productCode is null)

begin

return

end

if (@serial is null)

begin

return

end

-- Get current date and use this for production date

select @prodDate = getdate()

-- Get default build group

select @buildGroup = build_group_id

from build_groups

where is_default = 1

-- Get default plant

select @plantID = plant_id

from plants

where is_default = 1

-- Get current model year

select @modelyear = convert(smallint, datepart(yyyy, @prodDate))

-- Get product id

select @productID = product_id

from products

where code = @productCode

-- Try to find unit

select top 1 @unitID = unit_id

from units

where serial = @serial

and model_year = @modelyear

and product_id = @productID

order by unit_id

-- Quit if unit already exists

if (@@rowcount = 1)

return

begin transaction

-- Insert Unit

insert into units

(product_id, plant_id, model_year, serial, build_group_id, prod_date, is_locked)

values

(@productID, @plantID, @modelyear, @serial, @buildGroup, @prodDate, 0)

set @unitID = scope_identity()

insert into unit_colors

(unit_id, color_type_id, color_id)

values

(@unitID, 1, 1)

commit

END

Open the Create Unit Form

In Form Composer click Open in the Forms tab.

The Open Form dialog opens.

Select Create Unit and click Open.

Add Serial Number Control

We'll start by modifying a control so that the serial number can be entered which will then be stored with the newly created unit in the database.

Click on the Generic Text Edit control.

Click the smart tag.

You are presented with a number of options.

Change the Maximum Text Length to 8.

Enter Serial number in the Prompt to display when control is empty.

Click outside the smart tag menu to close it.

In the properties pane enable Expert Mode by clicking .

In the (Name) property enter Serial_Number. This will be useful later when you need to link the form to the stored procedure.

Add Product Code Control

The next control will present the operator with a list of product codes to select from.

Click on the Generic Listbox control.

Click the smart tag.

You are presented with a number of options. We need to use this control to retrieve the code of the products. The code will be presented to the operator who can then select one. The selected color code will then be sent to the stored procedure so that it can be associated to the new unit.

Click Edit Query.

In the upper pane enter the following text which will retrieve the code of each entry in the products table.

select code from products

Click Execute Query.

The lower pane lists the code for each product in the database. This is the list that will be presented to the operator. The list is generated from the database at runtime so it will always contain the latest information.

Click OK. You're returned to the smart tag menu.

Now we need to select a Value Member and a Display Member. The Value Member is what will be sent to the stored procedure. The Display Member is what will be shown to the operator.

Select code for the Value Member and for the Display Member.

Click outside of the smart tag menu to close it.

In the (Name) property in the properties pane enter Product_Code.

Add Color Control

The other control we need will allow the operator to select the color of the unit. This is a bit more complicated as we need to get the list of colors from the database.

Click on the Generic Listbox control.

Click the smart tag.

You are presented with a number of options. We need to use this control to retrieve the ID and description of the colors. The descriptions will be presented to the operator who can then select one. The ID of the selected color will then be sent to the stored procedure so that it can be associated to the new unit.

Click Edit Query.

In the upper pane enter the following text which will retrieve the ID and description of each entry in the colors table.

select color_id, descriptions from colors

Click Execute Query.

The lower pane lists the ID and descriptions for each color. However, you will notice that the description is set up so that it can display the name in every language. We just want to display the English version.

In the upper pane enter the following text.

select color_id, dbo.get_multi_text(c.descriptions, 1033) as colors from colors as c

Click Execute Query.

It now just shows the description in English.

Using and modifying sql queries requires a lot of sql knowledge which we won't cover in this tutorial.

Click OK. You're returned to the smart tag menu.

Select color_id for the Value Member. and colors for the Display Member.

Enter Select unit color in the Empty Text property.

Click outside of the smart tag menu to close it.

In the (Name) property in the properties pane enter Color.

Tidying the Form

Finally, we need to remove controls that aren't required.

Remove the Generic Checkbox control.

Adjust the other controls to fill the space.

The form is now ready to use.

Link Form to Stored Procedure

You now have everything you need to enable the stored procedure to create a new unit in the database. You just need to link the values from the components with the stored procedure.

Select the form by clicking at the top of it.

In the Properties pane click the edit button () next to OnAcceptStoredProcedure.

Select the custom_create_new_unit stored procedure from the drop-down list.

The Parameters pane lists the parameters that are required by and returned by the stored procedure. If Is Output is enabled for a parameter then that parameter will be returned to the Create Unit form. In this instance this will be the Unit ID.

Select the @productCode parameter. The Properties tab updates.

In the Properties pane select the Parameter Type drop-down list. This can be one of the following:

Value: A static value that will always be used by the stored procedure. This requires a number type and the value that will be used.

Component: Uses the output (Value Member) from one of the controls in the form. This is selected from the Component drop-down list which gives the names of the available controls.

Variable: Contains global variables from the database. This includes any variable relating to the unit, the station or the user. This requires the Variable Path to be specified.

Select Component.

The property list updates.

Select Product_Code for the Component property.

The @productcode parameter is now set up correctly.

Repeat the steps for @serial and @colorID input parameters.

Click OK.

Using the Form

The Create Unit form is accessed from the Main screen by clicking on the Commit button on the Go to unit control.

The form will open and you can enter the required details.

When you click OK the unit will be created and selected at the station.

To use this form the station property On commit must be set to Prompt. If you've been following this tutorial then you'll already have done this.